CoderWithAI LogoCoderWithAI

数据库设计和实现教程

1. SQL 简介和历史

1.1 SQL 简介

SQL（Structured Query Language）是一种用于管理关系数据库的标准语言。它允许用户对数据库进行查询、插入、更新和删除操作。

1.2 SQL 历史

SQL 最初由 IBM 在 1970 年代开发，用于管理关系数据库。1986 年，SQL 成为国际标准，并被广泛应用于各种数据库管理系统（DBMS）中。

2. 关系数据库概念

2.1 关系数据库

关系数据库是一种基于关系模型的数据库，数据存储在表中，表由行和列组成。每个表代表一个实体，每行代表一个记录，每列代表一个属性。

2.2 主键和外键

  • 主键：唯一标识表中每一行的列或列组合。
  • 外键：用于建立和强制表之间关系的列。

3. 安装和配置数据库管理系统

3.1 安装 MySQL

  1. 下载 MySQL 安装包。
  2. 运行安装程序，按照提示完成安装。
  3. 配置 MySQL 服务。

3.2 配置 MySQL

  1. 启动 MySQL 服务。
  2. 使用命令行工具或图形界面工具连接到 MySQL。

4. 基本 SQL 语法

4.1 创建数据库

CREATE DATABASE mydatabase;

4.2 使用数据库

USE mydatabase;

5. 创建和管理数据库

5.1 创建数据库

CREATE DATABASE mydatabase;

5.2 删除数据库

DROP DATABASE mydatabase;

6. 创建表 (CREATE TABLE)

6.1 创建表

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

7. 修改表结构 (ALTER TABLE)

7.1 添加列

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

7.2 删除列

ALTER TABLE employees DROP COLUMN department;

8. 删除表 (DROP TABLE)

8.1 删除表

DROP TABLE employees;

9. 索引创建和管理

9.1 创建索引

CREATE INDEX idx_name ON employees(name);

9.2 删除索引

DROP INDEX idx_name ON employees;

10. 视图创建和管理

10.1 创建视图

CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 5000;

10.2 删除视图

DROP VIEW high_salary_employees;

11. 插入数据 (INSERT)

11.1 插入数据

INSERT INTO employees (id, name, salary) VALUES (1, 'John Doe', 6000);

12. 更新数据 (UPDATE)

12.1 更新数据

UPDATE employees SET salary = 7000 WHERE id = 1;

13. 删除数据 (DELETE)

13.1 删除数据

DELETE FROM employees WHERE id = 1;

14. 基本查询 (SELECT)

14.1 查询数据

SELECT * FROM employees;

15. WHERE 子句和条件过滤

15.1 使用 WHERE 子句

SELECT * FROM employees WHERE salary > 5000;

16. 排序 (ORDER BY)

16.1 排序数据

SELECT * FROM employees ORDER BY salary DESC;

17. 分组 (GROUP BY)

17.1 分组数据

SELECT department, AVG(salary) FROM employees GROUP BY department;

18. 聚合函数 (SUM, AVG, COUNT, MAX, MIN)

18.1 使用聚合函数

SELECT SUM(salary) FROM employees;

19. HAVING 子句

19.1 使用 HAVING 子句

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

20. 子查询

20.1 使用子查询

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

21. 表连接 (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN)

21.1 使用 INNER JOIN

SELECT employees.name, departments.name FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

22. 自连接

22.1 使用自连接

SELECT e1.name AS employee, e2.name AS manager FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

23. UNION 和 UNION ALL

23.1 使用 UNION

SELECT name FROM employees
UNION
SELECT name FROM customers;

24. 相关子查询

24.1 使用相关子查询

SELECT name FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id);

25. 字符串函数

25.1 使用字符串函数

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

26. 数值函数

26.1 使用数值函数

SELECT ABS(-10);

27. 日期和时间函数

27.1 使用日期和时间函数

SELECT NOW();

28. 条件表达式 (CASE WHEN)

28.1 使用 CASE WHEN

SELECT name, salary,
CASE
    WHEN salary > 5000 THEN 'High'
    ELSE 'Low'
END AS salary_level
FROM employees;

29. 类型转换

29.1 使用类型转换

SELECT CAST('123' AS INT);

30. 事务概念

30.1 事务概念

事务是一组 SQL 语句，它们要么全部执行成功，要么全部失败。

31. ACID 属性

31.1 ACID 属性

  • 原子性：事务是不可分割的工作单位。
  • 一致性：事务执行前后，数据库必须保持一致状态。
  • 隔离性：并发执行的事务之间互不干扰。
  • 持久性：事务一旦提交，其结果是永久性的。

32. 事务控制语句 (BEGIN, COMMIT, ROLLBACK)

32.1 使用事务控制语句

BEGIN;
UPDATE employees SET salary = 7000 WHERE id = 1;
COMMIT;

33. 事务隔离级别

33.1 事务隔离级别

  • 读未提交：允许读取未提交的数据。
  • 读已提交：只允许读取已提交的数据。
  • 可重复读：在同一事务中多次读取同一数据时，结果一致。
  • 串行化：事务串行执行，避免并发问题。

34. 索引类型和创建

34.1 创建索引

CREATE INDEX idx_name ON employees(name);

35. 执行计划分析

35.1 分析执行计划

EXPLAIN SELECT * FROM employees WHERE salary > 5000;

36. 查询优化技巧

36.1 查询优化技巧

  • 使用索引。
  • 避免全表扫描。
  • 减少子查询的使用。

37. 性能监控和调优

37.1 性能监控

使用数据库提供的监控工具，如 MySQL 的 SHOW PROCESSLIST。

38. 创建和使用存储过程

38.1 创建存储过程

DELIMITER //
CREATE PROCEDURE GetEmployeeSalary(IN emp_id INT)
BEGIN
    SELECT salary FROM employees WHERE id = emp_id;
END //
DELIMITER ;

39. 创建和使用自定义函数

39.1 创建自定义函数

DELIMITER //
CREATE FUNCTION GetEmployeeName(emp_id INT) RETURNS VARCHAR(100)
BEGIN
    RETURN (SELECT name FROM employees WHERE id = emp_id);
END //
DELIMITER ;

40. 参数传递

40.1 传递参数

CALL GetEmployeeSalary(1);

41. 错误处理

41.1 错误处理

BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;
    START TRANSACTION;
    UPDATE employees SET salary = 7000 WHERE id = 1;
    COMMIT;
END;

42. 触发器概念

42.1 触发器概念

触发器是与表相关联的特殊存储过程，当表发生特定事件时自动执行。

43. 创建和管理触发器

43.1 创建触发器

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.name = UPPER(NEW.name);
END;

44. 触发器类型 (BEFORE, AFTER, INSTEAD OF)

44.1 触发器类型

  • BEFORE：在事件发生前触发。
  • AFTER：在事件发生后触发。
  • INSTEAD OF：替代事件执行。

45. 触发器应用场景

45.1 应用场景

  • 数据验证。
  • 自动更新相关表。

46. 视图概念和用途

46.1 视图概念

视图是基于 SQL 查询结果的虚拟表，可以简化复杂查询。

47. 创建和管理视图

47.1 创建视图

CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 5000;

48. 可更新视图

48.1 可更新视图

视图可以像表一样进行更新操作。

49. 物化视图 (特定数据库系统)

49.1 物化视图

物化视图是实际存储查询结果的视图，适用于频繁查询的场景。

50. 用户创建和管理

50.1 创建用户

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

51. 授权和撤销权限

51.1 授权

GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';

51.2 撤销权限

REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';

52. 角色管理

52.1 角色管理

CREATE ROLE 'developer';
GRANT SELECT, INSERT ON mydatabase.* TO 'developer';
GRANT 'developer' TO 'newuser'@'localhost';

53. 数据加密

53.1 数据加密

使用数据库提供的加密功能，如 MySQL 的 AES_ENCRYPT 和 AES_DECRYPT。

54. 窗口函数

54.1 窗口函数

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

55. 公共表表达式 (CTE)

55.1 使用 CTE

WITH high_salary_employees AS (
    SELECT * FROM employees WHERE salary > 5000
)
SELECT * FROM high_salary_employees;

56. 递归查询

56.1 递归查询

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

57. PIVOT 和 UNPIVOT 操作

57.1 PIVOT 操作

SELECT * FROM (
    SELECT department, salary FROM employees
) AS source
PIVOT (
    SUM(salary) FOR department IN ('Sales', 'HR', 'IT')
);

58. 数据仓库架构

58.1 数据仓库架构

数据仓库是用于支持决策分析的数据存储系统，通常采用星型或雪花型模型。

59. 维度建模

59.1 维度建模

维度建模是一种用于数据仓库的设计方法，包括事实表和维度表。

60. ETL 过程

60.1 ETL 过程

ETL（Extract, Transform, Load）是将数据从源系统提取、转换并加载到目标系统的过程。

61. OLAP 操作

61.1 OLAP 操作

OLAP（Online Analytical Processing）是用于多维数据分析的操作，如切片、切块、钻取等。

62. 关系型数据库与 NoSQL

62.1 关系型数据库与 NoSQL

关系型数据库适用于结构化数据，NoSQL 数据库适用于非结构化或半结构化数据。

63. JSON 数据类型和操作

63.1 JSON 数据类型

CREATE TABLE employees (
    id INT PRIMARY KEY,
    info JSON
);

64. 全文搜索

64.1 全文搜索

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword');

65. 实体关系图 (ERD)

65.1 ERD

ERD（Entity-Relationship Diagram）是用于表示数据库中实体及其关系的图表。

66. 范式化和反范式化

66.1 范式化

范式化是将数据分解为更小的表，以减少数据冗余。

66.2 反范式化

反范式化是将数据合并到一个大表中，以提高查询性能。

67. 设计模式和最佳实践

67.1 设计模式

常见的设计模式包括单例模式、工厂模式等。

67.2 最佳实践

  • 使用索引。
  • 避免全表扫描。
  • 使用事务。

68. 备份和恢复

68.1 备份

mysqldump -u username -p mydatabase > backup.sql

68.2 恢复

mysql -u username -p mydatabase < backup.sql

69. 日志管理

69.1 日志管理

使用数据库提供的日志管理工具，如 MySQL 的 mysqlbinlog。

70. 性能监控

70.1 性能监控

使用数据库提供的监控工具，如 MySQL 的 SHOW PROCESSLIST。

71. 数据库迁移

71.1 数据库迁移

使用工具如 mysqldump 和 mysqlimport 进行数据库迁移。

72. 分片技术

72.1 分片技术

分片是将数据分布到多个数据库实例中，以提高性能和可扩展性。

73. 分布式查询处理

73.1 分布式查询处理

分布式

← Previous: 20. 项目实战Next: 复杂查询和报表生成教程 →